package cayte.xiami.in.helper;

import java.util.ArrayList;

import cayte.xiami.in.AccountInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQL extends SQLiteOpenHelper {

	private static final String DB_NAME = "ACCOUNT_DB";
	private static final int VERSION = 2;

	public static final String TABLE = "table_account";
	public static final String ID = "id";
	public static final String NAME = "name";
	public static final String PASS = "pass";
	public static final String LAST = "last";
	public static final String DAY = "day";
	public static final String INTEGRAL = "integral";
	public static final String LEVEL = "level";
	public static final String COOKIE = "cookie";

	private ArrayList<AccountInfo> accs = null;

	public SQL(Context context) {
		super(context, DB_NAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE + " (" + ID
				+ " INTEGER PRIMARY KEY," + NAME + " VARCHAR," + PASS
				+ " VARCHAR," + LAST + " VARCHAR," + DAY + " INTEGER,"
				+ INTEGRAL + " VARCHAR," + LEVEL + " VARCHAR," + COOKIE
				+ " VARCHAR" + ")");
		if (accs != null && !accs.isEmpty()) {
			insertNewTable(db, accs);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		accs = queryOldTable(db);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE);
		onCreate(db);
	}

	public ArrayList<AccountInfo> queryOldTable(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		ArrayList<AccountInfo> accs = new ArrayList<AccountInfo>();
		try {
			Cursor c = db.query(SQL.TABLE, null, null, null, null, null, SQL.ID
					+ " desc", null);
			c.moveToFirst();
			AccountInfo acc = null;
			for (int i = 0; i < c.getCount(); i++) {
				acc = new AccountInfo();
				acc.name = c.getString(c.getColumnIndex(SQL.NAME));
				acc.pass = c.getString(c.getColumnIndex(SQL.PASS));
				acc.lastCheckin = c.getString(c.getColumnIndex(SQL.LAST));
				acc.day = c.getInt(c.getColumnIndex(SQL.DAY));
				acc.integral = c.getString(c.getColumnIndex(SQL.INTEGRAL));
				acc.level = c.getString(c.getColumnIndex(SQL.LEVEL));
				accs.add(0, acc);
				acc = null;
				c.moveToNext();
			}
			c.close();
			return accs;
		} catch (Exception e) {
			return accs;
		}
	}

	public void insertNewTable(SQLiteDatabase db, ArrayList<AccountInfo> accs) {
		// TODO Auto-generated method stub
		try {
			for (AccountInfo acc : accs) {
				ContentValues tcv = new ContentValues();
				tcv.put(SQL.NAME, acc.name);
				tcv.put(SQL.PASS, acc.pass);
				tcv.put(SQL.LAST, acc.lastCheckin);
				tcv.put(SQL.DAY, acc.day);
				tcv.put(SQL.INTEGRAL, acc.integral);
				tcv.put(SQL.LEVEL, acc.level);
				tcv.put(SQL.COOKIE, acc.cookie);
				db.insert(SQL.TABLE, null, tcv);
			}
		} catch (Exception e) {
		}
	}

}